package com.fowo.api.documentCirculation.sql;

import com.fowo.api.common.util.ContextHolder;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.Setter;
import lombok.experimental.Accessors;
import lombok.extern.slf4j.Slf4j;
import org.jetbrains.annotations.NotNull;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;
import java.util.*;
import java.util.function.Consumer;

/**
 * 用于实现与数据库语言无关的查询语句生成工具
 * @author yl_ls
 */
@Slf4j
public class SqlBuilder {
    public static final DbType DEFAULT_DB_TYPE = DbType.MySql;
    public static final Map<DbType, ISqlHandler> HANDLERS = new HashMap<>();

    static {
        HANDLERS.put(DbType.MySql, new MySqlHandler());
        HANDLERS.put(DbType.SqlServer, new SqlServerHandler());
    }

    private final DbType dbType;
    private final QueryKind queryKind;
    private final String table;
    private final Set<SelectColumn> selectColumns = new HashSet<>();
    private final Map<String, Object> values = new HashMap<>();
    private final List<ConditionPart> conditionParts = new ArrayList<>();
    private final List<JoinBuilder> joinBuilders = new ArrayList<>();
    private final List<String> groupBys = new ArrayList<>();
    private Integer skip;
    private Integer limit;
    private String asName = "t";

    SqlBuilder(DbType dbType, QueryKind queryKind, String table) {
        this.dbType = dbType;
        this.queryKind = queryKind;
        this.table = table;
        if (queryKind != QueryKind.Select) {
            this.asName = null;
        }
    }

    @Deprecated
    public static SqlBuilder select(String table) {
        return new SqlBuilder(DEFAULT_DB_TYPE, QueryKind.Select, table);
    }

    public static SqlBuilder select(DbType dbType, String table) {
        return new SqlBuilder(dbType, QueryKind.Select, table);
    }

    @Deprecated
    public static SqlBuilder insert(String table) {
        return new SqlBuilder(DEFAULT_DB_TYPE, QueryKind.Insert, table);
    }

    public static SqlBuilder insert(DbType dbType, String table) {
        return new SqlBuilder(dbType, QueryKind.Insert, table);
    }

    @Deprecated
    public static SqlBuilder update(String table) {
        return new SqlBuilder(DEFAULT_DB_TYPE, QueryKind.Update, table);
    }

    public static SqlBuilder update(DbType dbType, String table) {
        return new SqlBuilder(dbType, QueryKind.Update, table);
    }

    public SqlBuilder innerJoin(String joinTable, String asName, Consumer<JoinBuilder> joinBuilderConsumer, Consumer<SqlBuilder> joinSqlBuilder) {
        final SqlBuilder joinSelect = SqlBuilder.select(dbType, joinTable);
        joinSelect.setAsName(asName);
        joinSqlBuilder.accept(joinSelect);
        final JoinBuilder joinBuilder = new JoinBuilder();
        joinBuilderConsumer.accept(joinBuilder);
        joinBuilders.add(joinBuilder
                .setJoinKind(JoinKind.Inner)
                .setSqlBuilder(joinSelect)
        );
        return this;
    }

    public SqlBuilder innerJoin(String joinTable, String asName, String leftColumn, String rightColumn, Consumer<SqlBuilder> joinSqlBuilder) {
        return innerJoin(joinTable, asName, joinBuilder -> joinBuilder.onPart(leftColumn, rightColumn), joinSqlBuilder);
    }

    public SqlBuilder leftJoin(String joinTable, String asName, Consumer<JoinBuilder> joinBuilderConsumer, Consumer<SqlBuilder> joinSqlBuilder) {
        final SqlBuilder joinSelect = SqlBuilder.select(dbType, joinTable);
        joinSelect.setAsName(asName);
        joinSqlBuilder.accept(joinSelect);
        final JoinBuilder joinBuilder = new JoinBuilder();
        joinBuilderConsumer.accept(joinBuilder);
        joinBuilders.add(joinBuilder
                .setJoinKind(JoinKind.Left)
                .setSqlBuilder(joinSelect)
        );
        return this;
    }

    public SqlBuilder leftJoin(String joinTable, String asName, CharSequence leftColumn, CharSequence rightColumn, Consumer<SqlBuilder> joinSqlBuilder) {
        return leftJoin(joinTable, asName, joinBuilder -> joinBuilder.onPart(leftColumn, rightColumn), joinSqlBuilder);
    }

    /**
     * 设置要跳过的记录数
     * @param skipNum 跳过的记录数
     * @return 自身
     */
    public SqlBuilder skip(Integer skipNum) {
        this.skip = skipNum;
        return this;
    }

    public SqlBuilder selectColumn(String column) {
        this.selectColumns.add(new SelectColumn().setName(column));
        return this;
    }

    public SqlBuilder selectColumn(String column, String asName) {
        this.selectColumns.add(new SelectColumn().setName(column).setAsName(asName));
        return this;
    }

    public SqlBuilder selectColumn(String column, String asName, boolean convertToString) {
        this.selectColumns.add(new SelectColumn().setName(column).setAsName(asName).setConvertToString(convertToString));
        return this;
    }

    public SqlBuilder selectExpression(String expression, String asName) {
        this.selectColumns.add(new SelectColumn().setExpression(expression).setAsName(asName));
        return this;
    }

    public SqlBuilder groupBy(String ...exps) {
        for(String exp : exps) {
            if (this.groupBys.contains(exp)) {
                continue;
            }
            this.groupBys.add(exp);
        }
        return this;
    }

    public List<String> getGroupBys() {
        return groupBys;
    }

    /**
     * 设置要返回的记录数
     * @param size 返回的记录数
     * @return 自身
     */
    public SqlBuilder limit(Integer size) {
        this.limit = size;
        return this;
    }

    /**
     * 设置值
     * @param column 字段名
     * @param value 设置值
     * @return 自身
     */
    public SqlBuilder set(String column, Object value) {
        values.put(column, value);
        return this;
    }

    /**
     * 批量设置值
     * @param values 设置值对
     * @return 自身
     */
    public SqlBuilder setMap(Map<String, Object> values) {
        this.values.putAll(values);
        return this;
    }

    /**
     * 批量设置值并跳过空值
     * @param values 设置值对
     * @return 自身
     */
    public SqlBuilder setMapSkipNullValue(Map<String, Object> values) {
        for (Map.Entry<String, Object> kv : values.entrySet()) {
            if (kv.getValue() == null) {
                continue;
            }
            this.values.put(kv.getKey(), kv.getValue());
        }
        return this;
    }

    /**
     * 提供方便链式调用的查询处理
     * @param consumer 调用方法，参数是自身
     * @return 自身
     */
    public SqlBuilder apply(Consumer<SqlBuilder> consumer) {
        consumer.accept(this);
        return this;
    }
    public SqlBuilder like(String column, String value) {
        conditionParts.add(
                new ConditionPart().
                        setColumnName(column).
                        setConditionKind(ConditionKind.Like)
                        .setConditionValue(value)
        );
        return this;
    }

    /**
     * 添加直接条件表达式
     * @param column 列
     * @param exp 表达式
     * @return 自身
     */
    public SqlBuilder andExp(CharSequence column, String exp) {
        conditionParts.add(
                new ConditionPart().
                        setColumnName(column).
                        setConditionKind(ConditionKind.Exp)
                        .setConditionValue(exp)
        );
        return this;
    }

    /**
     * 添加等于过滤
     * @param column 字段名
     * @param value 等于值
     * @return 自身
     */
    public SqlBuilder eq(String column, Object value) {
        conditionParts.add(
                new ConditionPart().
                        setColumnName(column).
                        setConditionKind(ConditionKind.Equals)
                        .setConditionValue(value)
        );
        return this;
    }

    /**
     * 添加不等于等于过滤
     * @param column 字段名
     * @param value 不等于值
     * @return 自身
     */
    public SqlBuilder ne(String column, Object value) {
        conditionParts.add(
                new ConditionPart().
                        setColumnName(column).
                        setConditionKind(ConditionKind.NotEquals)
                        .setConditionValue(value)
        );
        return this;
    }

    /**
     * 添加大于过滤
     * @param column 字段名
     * @param value 大于此值
     * @return 自身
     */
    public SqlBuilder gt(String column, Object value) {
        conditionParts.add(
                new ConditionPart().
                        setColumnName(column).
                        setConditionKind(ConditionKind.GreaterThan)
                        .setConditionValue(value)
        );
        return this;
    }

    public SqlBuilder in(String column, Object value) {
        conditionParts.add(
                new ConditionPart().
                        setColumnName(column).
                        setConditionKind(ConditionKind.In)
                        .setConditionValue(value)
        );
        return this;
    }

    public SqlBuilder notIn(String column, Object value) {
        conditionParts.add(
                new ConditionPart().
                        setColumnName(column).
                        setConditionKind(ConditionKind.NotIn)
                        .setConditionValue(value)
        );
        return this;
    }

    public SqlBuilder orGroup(Consumer<List<SqlBuilder.ConditionPart>> consumer) {
        List<SqlBuilder.ConditionPart> orParts = new ArrayList<>();
        consumer.accept(orParts);
        conditionParts.add(new ConditionPart().setConditionKind(ConditionKind.OrGroup).setConditionValue(orParts));
        return this;
    }

    public QueryKind getQueryKind() {
        return queryKind;
    }

    public String getTable() {
        return table;
    }

    public List<ConditionPart> getConditionParts() {
        return conditionParts;
    }

    public List<JoinBuilder> getJoinBuilders() {
        return joinBuilders;
    }

    public Map<String, Object> getValues() {
        return values;
    }

    /**
     * 运行查询，返回总记录数
     * @return 总记录数
     * @throws Exception 查询异常时触发
     */
    public Long executeSelectCount() throws Exception {
        if (queryKind != QueryKind.Select) {
            throw new Exception("查询类型错误，此操作只支持 Select 类型");
        }
        final ISqlHandler sqlHandler = getSqlHandler();
        final SqlAndArgs sql = sqlHandler.getCountSql(this);
        log.debug(sql.toDebugString());
        final JdbcTemplate jdbcTemplate = getJdbcTemplate();
        return jdbcTemplate.queryForObject(sql.getSql(), Long.class, sql.toArgsArray());
    }

    /**
     * 运行统计查询，返回指定统计信息
     * @param columns 统计列信息
     * @return 返回查询结果
     */
    public Map<String, Object> executeSelectStatistics(List<StatisticsColumn> columns) throws Exception {
        if (queryKind != QueryKind.Select) {
            throw new Exception("查询类型错误，此操作只支持 Select 类型");
        }
        final ISqlHandler sqlHandler = getSqlHandler();
        final SqlAndArgs sql = sqlHandler.getStatisticsSql(this, columns);
        log.debug(sql.toDebugString());
        final JdbcTemplate jdbcTemplate = getJdbcTemplate();
        try {
            return jdbcTemplate.queryForMap(sql.getSql(), sql.toArgsArray());
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    /**
     * 运行查询，返回第一行
     * @return 结果集第一行数据
     * @throws Exception 查询异常时触发
     */
    public Map<String, Object> executeSelect() throws Exception {
        if (queryKind != QueryKind.Select) {
            throw new Exception("查询类型错误，此操作只支持 Select 类型");
        }
        final ISqlHandler sqlHandler = getSqlHandler();
        final SqlAndArgs sql = sqlHandler.getSql(this);
        log.debug(sql.toDebugString());
        final JdbcTemplate jdbcTemplate = getJdbcTemplate();
        try {
            return jdbcTemplate.queryForMap(sql.getSql(), sql.toArgsArray());
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    /**
     * 运行查询，返回列表
     * @return 结果集列表数据
     * @throws Exception 查询异常时触发
     */
    public List<Map<String, Object>> executeSelectList() throws Exception {
        if (queryKind != QueryKind.Select) {
            throw new Exception("查询类型错误，此操作只支持 Select 类型");
        }
        final ISqlHandler sqlHandler = getSqlHandler();
        final SqlAndArgs sql = sqlHandler.getSql(this);
        log.debug(sql.toDebugString());
        final JdbcTemplate jdbcTemplate = getJdbcTemplate();
        return jdbcTemplate.queryForList(sql.getSql(), sql.toArgsArray());
    }

    public boolean executeSelectExists() throws Exception {
        if (queryKind != QueryKind.Select) {
            throw new Exception("查询类型错误，此操作只支持 Select 类型");
        }
        final ISqlHandler sqlHandler = getSqlHandler();
        final SqlAndArgs sql = sqlHandler.getSelectExistsSql(this);
        log.debug(sql.toDebugString());
        final JdbcTemplate jdbcTemplate = getJdbcTemplate();
        return Boolean.TRUE.equals(jdbcTemplate.queryForObject(sql.getSql(), Boolean.class, sql.toArgsArray()));
    }

    public DbType getDbType() {
        return dbType;
    }

    /**
     * 运行插入操作
     * @throws Exception 查询异常时触发
     */
    public void executeInsert() throws Exception {
        if (queryKind != QueryKind.Insert) {
            throw new Exception("查询类型错误，此操作只支持 Insert 类型");
        }
        final ISqlHandler sqlHandler = getSqlHandler();
        final SqlAndArgs sql = sqlHandler.getSql(this);
        final JdbcTemplate jdbcTemplate = getJdbcTemplate();
        log.debug(sql.toDebugString());
        if(jdbcTemplate.update(sql.getSql(), sql.toArgsArray()) != 1) {
            throw new Exception("插入操作失败");
        }
    }

    /**
     * 运行更新操作
     * @return 更新的记录数
     * @throws Exception 查询异常时触发
     */
    public int executeUpdate() throws Exception {
        if (queryKind != QueryKind.Update) {
            throw new Exception("查询类型错误，此操作只支持 Update 类型");
        }
        final ISqlHandler sqlHandler = getSqlHandler();
        final SqlAndArgs sql = sqlHandler.getSql(this);
        log.debug(sql.toDebugString());
        final JdbcTemplate jdbcTemplate = getJdbcTemplate();
        return jdbcTemplate.update(sql.getSql(), sql.toArgsArray());
    }

    /**
     * 运行删除操作
     * @return 删除的记录数
     * @throws Exception 查询异常时触发
     */
    public int executeDelete() throws Exception {
        if (queryKind != QueryKind.Delete) {
            throw new Exception("查询类型错误，此操作只支持 Delete 类型");
        }
        final ISqlHandler sqlHandler = getSqlHandler();
        final SqlAndArgs sql = sqlHandler.getSql(this);
        log.debug(sql.toDebugString());
        final JdbcTemplate jdbcTemplate = getJdbcTemplate();
        return jdbcTemplate.update(sql.getSql(), sql.toArgsArray());
    }

    public ISqlHandler getSqlHandler() {
        return HANDLERS.get(dbType);
    }

    private JdbcTemplate jdbcTemplate;
    public JdbcTemplate getJdbcTemplate() {
        if (jdbcTemplate == null) {
            jdbcTemplate = new JdbcTemplate(ContextHolder.getApplicationContext().getBean(DataSource.class));
        }
        return jdbcTemplate;
    }

    public Integer getSkip() {
        return skip;
    }

    public Integer getLimit() {
        return limit;
    }

    public Set<SelectColumn> getSelectColumns() {
        return selectColumns;
    }

    public String getAsName() {
        return asName;
    }

    public SqlBuilder setAsName(String asName) {
        this.asName = asName;
        return this;
    }

    public SqlAndArgs getSqlSelectList() throws Exception {
        if (queryKind != QueryKind.Select) {
            throw new Exception("查询类型错误，此操作只支持 Select 类型");
        }
        final ISqlHandler sqlHandler = getSqlHandler();
        final SqlAndArgs sql = sqlHandler.getSql(this);
        return sql;
    }

    public enum QueryKind {
        Select,
        Insert,
        Update,
        Delete
    }

    public enum ConditionKind {
        Like,
        Equals,
        NotEquals,
        GreaterThan,
        In,
        NotIn,
        Exp,
        OrGroup,
        AndGroup,
        IsNotNull,
        IsNull,
    }

    @Getter
    @Setter
    @Accessors(chain = true)
    public static class ConditionPart {
        private String tableAsName;
        private CharSequence columnName;
        private ConditionKind conditionKind;
        private Object conditionValue;
    }

    @Getter
    @Setter
    @Accessors(chain = true)
    public static class JoinBuilder {
        private JoinKind joinKind;
        private SqlBuilder sqlBuilder;
        private String sql;
        private List<JoinOnPart> onParts = new ArrayList<>();

        public JoinBuilder onPart (CharSequence leftColumn, CharSequence rightColumn) {
            JoinOnPart joinOnPart = new JoinOnPart();
            joinOnPart.setLeftColumn(leftColumn);
            joinOnPart.setRightColumn(rightColumn);
            onParts.add(joinOnPart);
            return this;
        }

        public JoinBuilder onPart (String leftTable, CharSequence leftColumn, String rightTable, CharSequence rightColumn) {
            JoinOnPart joinOnPart = new JoinOnPart();
            joinOnPart.setLeftTable(leftTable);
            joinOnPart.setLeftColumn(leftColumn);
            joinOnPart.setRightTable(rightTable);
            joinOnPart.setRightColumn(rightColumn);
            onParts.add(joinOnPart);
            return this;
        }

        public JoinBuilder onPart (String leftTable, CharSequence leftColumn, CharSequence rightExp) {
            JoinOnPart joinOnPart = new JoinOnPart();
            joinOnPart.setLeftTable(leftTable);
            joinOnPart.setLeftColumn(leftColumn);
            joinOnPart.setRightColumn(rightExp);
            joinOnPart.setNoConnector(true);
            onParts.add(joinOnPart);
            return this;
        }
    }

    @Getter
    @Setter
    @Accessors(chain = true)
    public static class JoinOnPart {
        private String leftTable;
        private CharSequence leftColumn;
        private String rightTable;
        private CharSequence rightColumn;
        private Boolean noConnector;
    }

    /**
     * 可用于 JoinOnPart 的 leftColumn 或 rightColumn 表示指定的值
     */
    public static class JoinOnConstValue implements CharSequence {

        private final String value;

        public JoinOnConstValue(String value) {
            this.value = value;
        }

        @Override
        public int length() {
            return value.length();
        }

        @Override
        public char charAt(int index) {
            return value.charAt(index);
        }

        @NotNull
        @Override
        public CharSequence subSequence(int start, int end) {
            return value.subSequence(start, end);
        }

        public String getValue() {
            return value;
        }

        @Override
        public String toString() {
            return value;
        }
    }

    public enum JoinKind {
        Left,
        Inner,
        Sql,
    }

    public enum DbType {
        MySql,
        SqlServer
    }

    @Getter
    @Setter
    @Accessors(chain = true)
    @EqualsAndHashCode
    public static class SelectColumn {
        private String name;
        private String expression;
        private String asName;
        private boolean convertToString;
    }

    public enum StatisticsKind {
        Sum,
        Avg,
        Min,
        Max,
        Expression
    }

    /**
     * 用于定义 selectStatistics 中的统计列信息
     */
    @Getter
    @Setter
    @Accessors(chain = true)
    public static class StatisticsColumn {
        private StatisticsKind kind;
        private String column;
        private boolean columnIsExpression;
        private String asName;

        public static StatisticsColumn sumByColumn(String column, String asName) {
            return new StatisticsColumn()
                    .setKind(StatisticsKind.Sum)
                    .setColumn(column)
                    .setAsName(asName);
        }

        public static StatisticsColumn sumByExpression(String expression, String asName) {
            return new StatisticsColumn()
                    .setKind(StatisticsKind.Sum)
                    .setColumnIsExpression(true)
                    .setColumn(expression)
                    .setAsName(asName);
        }
    }
}
